package br.edu.ifpb.classesDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.edu.ifpb.conexao.ConnectionFactory;
import br.edu.ifpb.entidades.Refeicao;
import br.edu.ifpb.sistema.DataHorario;


public class RefeicaoDAO implements DAO<Refeicao> {
	
	Connection connection;
	
	public RefeicaoDAO(){
		connection = new ConnectionFactory().getConnection();
	}
	
	@Override
	public boolean insert(Refeicao value) throws SQLException {

			String sql = "insert into tb_refeicao (id_usuario,tipo_refeicao) values (?,?)";
			PreparedStatement stmt = connection.prepareStatement(sql);
			
			stmt.setInt(1, value.getId_usuario());
			stmt.setString(2, value.getTipo());
			
			stmt.execute();
			
			return true;
			
		
	}

	@Override
	public ArrayList<Refeicao> getAll() throws SQLException {
		
		DataHorario d = new DataHorario();
		ArrayList<Refeicao> lista = new ArrayList<Refeicao>();
		
		PreparedStatement stmt = connection.prepareStatement("select * from tb_refeicao where data_refeicao like '" + d.getData() + "%' order by tipo_refeicao");
		ResultSet rs = stmt.executeQuery();
		Refeicao r;
		
		while (rs.next()) {
			r = new Refeicao();
			
			PreparedStatement stmt2 = connection.prepareStatement("select * from tb_usuario");
			ResultSet rs2 = stmt2.executeQuery();
			
			int num = 1;
			
			while (num == 1 && rs2.next()){
				if (rs.getInt("id_usuario") == rs2.getInt("id_usuario")){
					num = 0;
					r.setId_usuario(rs2.getInt("id_usuario"));
					r.setNome_usuario(rs2.getString("nome"));
				}
			}
			
			r.setId_refeicao(rs.getInt("id_refeicao"));
			r.setData(d.getData());
			r.setTipo(rs.getString("tipo_refeicao"));
			
			lista.add(r);
		}
		
		return lista;
	}

	@Override
	public Refeicao get(int id) throws SQLException {
		
		Refeicao r = null;
		
		String sql = "select * tb_refeicao where id_refeicao = " + id;
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		
		if (rs.next()){
			
			r = new Refeicao();
			
			r.setId_refeicao(id);
			r.setData(rs.getString("data_refeicao"));
			r.setId_usuario(rs.getInt("id_usuario"));
			r.setTipo(rs.getString("tipo_refeicao"));
			
		}
		
		return r;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		
		try {
			
			String sql = "delete from tb_refeicao where id_refeicao = " + id;
			PreparedStatement stmt = connection.prepareStatement(sql);
			
			stmt.execute();
			
			return true;
			
		} catch (SQLException e){
			return false;
		}
	}

	@Override
	public void update(Refeicao value) throws SQLException {
		
		String sql = "update tb_refeicao set tipo = ?, id_usuario = ? where id_refeicao = ?";
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		stmt.setString(1, value.getTipo());
		stmt.setInt(2, value.getId_usuario());
		stmt.setInt(3, value.getId_refeicao());
				
		stmt.execute();
		
	}

	@Override
	public void disconnect() throws SQLException {
		this.connection.close();
	}
	
	public int verificaConfirmacao (int cod, String refeicao) throws SQLException{
		
		DataHorario d = new DataHorario();
		
		String sql = "select * from tb_refeicao where data_refeicao = '" + d.toString() + "'";
		PreparedStatement stmt = connection.prepareStatement(sql);			
		ResultSet rs = stmt.executeQuery();
		
		while (rs.next()) {
			int cod2 = rs.getInt("id_usuario");
			String refeicao2 = rs.getString("tipo_refeicao");
			
			if (cod2 == cod && refeicao2.equals(refeicao))
				return 1;
		}
		
		if ((refeicao.equalsIgnoreCase("A") && d.getHora() > 9) || (refeicao.equalsIgnoreCase("J") && d.getHora() > 20))
			return 2;
		
		if ((refeicao.equalsIgnoreCase("A") && d.getHora() == 9) || (refeicao.equalsIgnoreCase("J") && d.getHora() == 14))
			if ((d.getMinuto() > 0 && refeicao.equals("A")) || (refeicao.equals("J") && d.getMinuto() > 30))
				return 2;
		
		return 0;
	}

}
